package cn.edu.shou.lab.config.utils;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.servlet.http.HttpServletResponse;
import java.io.*;
import java.util.ArrayList;
import java.util.List;
import java.util.logging.Logger;

public class ExcelUtil {

    private final static Logger LOGGER = Logger.getLogger(ExcelUtil.class.getName());

    /**
     * 提取Excel内容
     * @param excelFile 待加载的excel文件（不可为空）
     * @param skipRows 跳过行数
     * @param skipCols 跳过列数
     * @param readRows 读取行数
     * @param readCols 读取列数
     * @param sheetName sheet名称
     * @return List<List<String>>
     */
    public static List<List<String>> read(File excelFile,
                                          Integer skipRows,
                                          Integer skipCols,
                                          Integer readRows,
                                          Integer readCols,
                                          String sheetName){
        if(excelFile == null || !excelFile.isFile()){
            throw new RuntimeException("excel file is not available !");
        }
        Workbook workbook = load(excelFile);
        return read(workbook,skipRows, skipCols, readRows, readCols, sheetName);
    }

    /**
     * 提取Excel内容
     * @param is 待加载的Excel输入流（不可为空）
     * @param skipRows 跳过行数
     * @param skipCols 跳过列数
     * @param readRows 读取行数
     * @param readCols 读取列数
     * @param ext excel文件后缀名
     * @param sheetName sheet名称
     * @return List<List<String>>
     */
    public static List<List<String>> read(InputStream is,
                                          Integer skipRows,
                                          Integer skipCols,
                                          Integer readRows,
                                          Integer readCols,
                                          String ext,
                                          String sheetName){
        if(is == null){
            throw new RuntimeException("excel input stream is not available !");
        }
        String defaultExt = "xlsx";
        if(ext == null){
            ext = defaultExt;
        }
        Workbook workbook = load(is, ext);
        return read(workbook, skipRows, skipCols, readRows, readCols, sheetName);
    }

    /**
     * 提取Excel内容
     * @param workbook 已经加载的excel（不可为空）
     * @param skipRows 跳过行数
     * @param skipCols 跳过列数
     * @param readRows 读取行数
     * @param readCols 读取列数
     * @param sheetName sheet名称
     * @return List<List<String>>
     */
    public static List<List<String>> read(Workbook workbook,
                                          Integer skipRows,
                                          Integer skipCols,
                                          Integer readRows,
                                          Integer readCols,
                                          String sheetName){
        Sheet sheet;
        Row row;
        Cell cell;
        List<List<String>> results = new ArrayList<>();
        if(workbook == null){
            throw new RuntimeException("workbook initialize failure !");
        }
        if(skipRows == null ){
            skipRows = 0;
        }
        if(skipCols == null ){
            skipCols = 0;
        }
        if(readRows == null ){
            readRows = Integer.MAX_VALUE;
        }
        if(readCols == null ){
            readCols = Integer.MAX_VALUE;
        }
        if(skipRows < 0 || skipCols < 0){
            throw new RuntimeException("skip rows or cols can not less than 0 !");
        }
        if(readRows < 0 || readCols < 0){
            throw new RuntimeException("read rows or cols can not less than 0 !");
        }
        if(sheetName == null || sheetName.isEmpty()){
            sheet = workbook.getSheetAt(0);
        }else {
            sheet = workbook.getSheet(sheetName);
        }
        for(int i = skipRows ; i <= sheet.getLastRowNum() && i < readRows + skipRows ; i ++){
            row = sheet.getRow(i);
            if(row != null){
                List<String> rowData = new ArrayList<>();
                for(int j = skipCols ; j <= row.getLastCellNum() && j < readCols ; j ++){
                    cell = row.getCell(j);
                    if(cell != null){
                        cell.setCellType(CellType.STRING);
                        rowData.add(cell.getStringCellValue());
                    }
                }
                results.add(rowData);
            }
        }
        return results;
    }

    /**
     * 写入Excel并返回二进制
     * @param workbook 读取到Excel的workbook
     * @param response HttpServletResponse
     * @throws IOException
     */
    public static void writeToResponse(Workbook workbook, HttpServletResponse response) throws IOException {
        if(workbook != null && response != null){
            workbook.write(response.getOutputStream());
            workbook.close();
        }else{
            throw new RuntimeException("workbook can not be null");
        }
    }

    /**
     * 写入Excel并保存文件
     * @param workbook 读取到Excel的workbook
     * @param xlsFile 写入的Excel文件
     * @throws IOException
     */
    public static void writeToFile(Workbook workbook, File xlsFile) throws IOException {
        if(workbook != null && xlsFile.isFile()){
            FileOutputStream fos = new FileOutputStream(xlsFile);
            workbook.write(fos);
            workbook.close();
        }else{
            throw new RuntimeException("workbook can not be null");
        }
    }

    /**
     * 将数据写入Workbook
     * @param xlsFile 读取的基础Excel
     * @param sheetName 读取的基础sheet名称
     * @param datas 待写入数据
     * @param skipRows 跳过行数
     * @param skipCols 跳过列数
     * @return Workbook
     */
    public static Workbook write(File xlsFile,
                                 String sheetName,
                                 List<List<String>> datas,
                                 Integer skipRows,
                                 Integer skipCols) {
        Workbook workbook = null;
        Sheet sheet;
        try {
            workbook = load(xlsFile);
            if(workbook == null){
                throw new RuntimeException("workbook initialize failure !");
            }
            if(!xlsFile.isFile()){
                throw new RuntimeException("excel file is not exist !");
            }
            if(datas == null || datas.size() == 0){
                throw new RuntimeException("data can not be null !");
            }
            if(skipRows == null ){
                skipRows = 0;
            }
            if(skipCols == null ){
                skipCols = 0;
            }
            if(skipRows < 0 || skipCols < 0){
                throw new RuntimeException("skip rows or cols can not less than 0 !");
            }
            try {
                if(sheetName == null || sheetName.isEmpty()){
                    // 创建工作表
                    sheet = workbook.getSheetAt(0);
                }else{
                    sheet = workbook.getSheet(sheetName);
                }
                if(sheet == null){
                    sheet = workbook.createSheet(sheetName);
                }
            }catch (Exception e){
                sheet = workbook.createSheet("new sheet");
            }

            // 添加数据
            for (int row = 0; row < datas.size(); row++) {
                Row dataRows = sheet.createRow(row + skipRows);
                for (int col = 0; col < datas.get(row).size(); col++) {
                    dataRows.createCell(col + skipCols).setCellValue(datas.get(row).get(col));
                }
            }
            return workbook;
        } catch (Exception e) {
            e.printStackTrace();
            LOGGER.warning(e.getMessage());
            return null;
        }
    }

    public static Workbook load(File file) {
        if(file == null || !file.isFile()){
            throw new RuntimeException("excel file is not available !");
        }
        Workbook workbook = null;
        try {
            FileInputStream fis = new FileInputStream(file);
            if (file.getName().endsWith(".xls")) {  //Excel 2003
                workbook = new HSSFWorkbook(fis);
            } else if (file.getName().endsWith(".xlsx")) {  // Excel 2007/2010
                workbook = new XSSFWorkbook(fis);
            }
            return workbook;
        } catch (Exception e) {
            e.printStackTrace();
            return null;
        }
    }

    public static Workbook load(InputStream is, String excelExt) {
        Workbook workbook = null;
        try {
            if (".xls".contains(excelExt)) {  //Excel 2003
                workbook = new HSSFWorkbook(is);
            } else if (".xlsx".contains(excelExt)) {  // Excel 2007/2010
                workbook = new XSSFWorkbook(is);
            }
        } catch (Exception e) {
            e.printStackTrace();
        }
        return workbook;
    }

    public static void removeAllSheet(File xlsFile) {
        Workbook workbook = null;
        try {
            workbook = load(xlsFile);
            int sheetNum = workbook.getNumberOfSheets();
            for (int i = 0; i < sheetNum; i++) {
                workbook.removeSheetAt(0);
            }
            FileOutputStream xlsStream = new FileOutputStream(xlsFile);
            workbook.write(xlsStream);
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            if (workbook != null) {
                try {
                    workbook.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}
